{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\r\n",
    "import numpy as np\r\n",
    "#读取数据\r\n",
    "df=pd.read_csv('D:\\study_notes\\master\\data\\dataAnalyst_sql.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>city</th>\n      <th>companyId</th>\n      <th>education</th>\n      <th>bottom</th>\n      <th>top</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2245819</td>\n      <td>上海</td>\n      <td>130876</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1605795</td>\n      <td>上海</td>\n      <td>58109</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2392372</td>\n      <td>北京</td>\n      <td>48294</td>\n      <td>硕士</td>\n      <td>4</td>\n      <td>8</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2527100</td>\n      <td>上海</td>\n      <td>57577</td>\n      <td>本科</td>\n      <td>3</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2531473</td>\n      <td>上海</td>\n      <td>7069</td>\n      <td>本科</td>\n      <td>4</td>\n      <td>6</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "   positionId city  companyId education  bottom top\n0     2245819   上海     130876        本科       2   3\n1     1605795   上海      58109        本科       2   4\n2     2392372   北京      48294        硕士       4   8\n3     2527100   上海      57577        本科       3   4\n4     2531473   上海       7069        本科       4   6"
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "#删除脏数据\n",
    "# lis = []\n",
    "# for i in range(len(df.bottom)):\n",
    "#     if (not df.bottom[i].strip().isdigit()) or (not df.top[i].strip().isdigit()):\n",
    "#         lis.append(i)\n",
    "# df = df.drop(lis)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 40 entries, 0 to 39\n",
      "Data columns (total 6 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   positionId  40 non-null     int64 \n",
      " 1   city        40 non-null     object\n",
      " 2   companyId   40 non-null     int64 \n",
      " 3   education   40 non-null     object\n",
      " 4   bottom      40 non-null     int64 \n",
      " 5   top         40 non-null     object\n",
      "dtypes: int64(3), object(3)\n",
      "memory usage: 2.0+ KB\n"
     ]
    }
   ],
   "source": [
    "#查看数据类型信息\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "从panda表中获取指定行的方法记录一下。\n",
    "df[df[\"列名\"] ==\"条件\"] 等价于 df.query(\"列名 == '条件'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>city</th>\n      <th>companyId</th>\n      <th>education</th>\n      <th>bottom</th>\n      <th>top</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2245819</td>\n      <td>上海</td>\n      <td>130876</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1605795</td>\n      <td>上海</td>\n      <td>58109</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2527100</td>\n      <td>上海</td>\n      <td>57577</td>\n      <td>本科</td>\n      <td>3</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2531473</td>\n      <td>上海</td>\n      <td>7069</td>\n      <td>本科</td>\n      <td>4</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>2549808</td>\n      <td>上海</td>\n      <td>47993</td>\n      <td>大专</td>\n      <td>7</td>\n      <td>14</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "   positionId city  companyId education  bottom top\n0     2245819   上海     130876        本科       2   3\n1     1605795   上海      58109        本科       2   4\n3     2527100   上海      57577        本科       3   4\n4     2531473   上海       7069        本科       4   6\n6     2549808   上海      47993        大专       7  14"
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#查询数据1\n",
    "df.query(\"city == '上海'\").head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "1    58109\n2    48294\n3    57577\nName: companyId, dtype: int64"
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#查询数据2\n",
    "df.loc[1:3,'companyId']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df.drop(25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "0      2.5\n1      3.0\n2      6.0\n3      3.5\n4      5.0\n5      7.0\n6     10.5\n7     15.0\n8     11.5\n9     15.0\n10    22.5\n11    15.0\n12    11.5\n13    15.0\n14    12.5\n15    12.5\n16    15.0\n17    12.5\n18    10.0\n19    11.5\n20    22.5\n21    14.0\n22     3.5\n23     6.5\n24    15.0\n26     9.0\n27    11.5\n28    12.0\n29    17.5\n30     7.0\n31    15.0\n32    15.0\n33    12.5\n34    19.0\n35    13.5\n36    15.0\n37    15.0\n38    20.0\n39     9.0\ndtype: float64"
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = df.bottom.astype(int)\n",
    "b = df.top.astype(int)\n",
    "(a+b)/2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>city</th>\n      <th>companyId</th>\n      <th>education</th>\n      <th>bottom</th>\n      <th>top</th>\n      <th>avg</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2245819</td>\n      <td>上海</td>\n      <td>130876</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>3</td>\n      <td>2.5</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1605795</td>\n      <td>上海</td>\n      <td>58109</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>4</td>\n      <td>3.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2392372</td>\n      <td>北京</td>\n      <td>48294</td>\n      <td>硕士</td>\n      <td>4</td>\n      <td>8</td>\n      <td>6.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "   positionId city  companyId education  bottom top  avg\n0     2245819   上海     130876        本科       2   3  2.5\n1     1605795   上海      58109        本科       2   4  3.0\n2     2392372   北京      48294        硕士       4   8  6.0"
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['avg']=(a+b)/2\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>city</th>\n      <th>companyId</th>\n      <th>education</th>\n      <th>bottom</th>\n      <th>top</th>\n      <th>avg</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10</th>\n      <td>2439826</td>\n      <td>上海</td>\n      <td>54000</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>30</td>\n      <td>22.5</td>\n    </tr>\n    <tr>\n      <th>20</th>\n      <td>2188681</td>\n      <td>上海</td>\n      <td>122536</td>\n      <td>本科</td>\n      <td>20</td>\n      <td>25</td>\n      <td>22.5</td>\n    </tr>\n    <tr>\n      <th>38</th>\n      <td>2224743</td>\n      <td>上海</td>\n      <td>75569</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>25</td>\n      <td>20.0</td>\n    </tr>\n    <tr>\n      <th>34</th>\n      <td>2375192</td>\n      <td>北京</td>\n      <td>148603</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>23</td>\n      <td>19.0</td>\n    </tr>\n    <tr>\n      <th>29</th>\n      <td>2393474</td>\n      <td>上海</td>\n      <td>78151</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>20</td>\n      <td>17.5</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "    positionId city  companyId education  bottom top   avg\n10     2439826   上海      54000        本科      15  30  22.5\n20     2188681   上海     122536        本科      20  25  22.5\n38     2224743   上海      75569        本科      15  25  20.0\n34     2375192   北京     148603        本科      15  23  19.0\n29     2393474   上海      78151        本科      15  20  17.5"
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(by='avg',ascending=False).head(5) #按照一个参数进行排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "20    22.5\n10    22.5\n38    20.0\n34    19.0\n29    17.5\nName: avg, dtype: float64"
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.avg.sort_values(ascending=False).head()#也可以使用df.avg的形式排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>city</th>\n      <th>companyId</th>\n      <th>education</th>\n      <th>bottom</th>\n      <th>top</th>\n      <th>avg</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10</th>\n      <td>2439826</td>\n      <td>上海</td>\n      <td>54000</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>30</td>\n      <td>22.5</td>\n    </tr>\n    <tr>\n      <th>20</th>\n      <td>2188681</td>\n      <td>上海</td>\n      <td>122536</td>\n      <td>本科</td>\n      <td>20</td>\n      <td>25</td>\n      <td>22.5</td>\n    </tr>\n    <tr>\n      <th>38</th>\n      <td>2224743</td>\n      <td>上海</td>\n      <td>75569</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>25</td>\n      <td>20.0</td>\n    </tr>\n    <tr>\n      <th>34</th>\n      <td>2375192</td>\n      <td>北京</td>\n      <td>148603</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>23</td>\n      <td>19.0</td>\n    </tr>\n    <tr>\n      <th>29</th>\n      <td>2393474</td>\n      <td>上海</td>\n      <td>78151</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>20</td>\n      <td>17.5</td>\n    </tr>\n    <tr>\n      <th>31</th>\n      <td>2452249</td>\n      <td>北京</td>\n      <td>15125</td>\n      <td>不限</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n    </tr>\n    <tr>\n      <th>36</th>\n      <td>2490990</td>\n      <td>北京</td>\n      <td>151935</td>\n      <td>大专</td>\n      <td>12</td>\n      <td>18</td>\n      <td>15.0</td>\n    </tr>\n    <tr>\n      <th>37</th>\n      <td>1978544</td>\n      <td>北京</td>\n      <td>75604</td>\n      <td>硕士</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>2028802</td>\n      <td>上海</td>\n      <td>10215</td>\n      <td>本科</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>2536296</td>\n      <td>上海</td>\n      <td>47993</td>\n      <td>本科</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "    positionId city  companyId education  bottom top   avg\n10     2439826   上海      54000        本科      15  30  22.5\n20     2188681   上海     122536        本科      20  25  22.5\n38     2224743   上海      75569        本科      15  25  20.0\n34     2375192   北京     148603        本科      15  23  19.0\n29     2393474   上海      78151        本科      15  20  17.5\n31     2452249   北京      15125        不限      10  20  15.0\n36     2490990   北京     151935        大专      12  18  15.0\n37     1978544   北京      75604        硕士      10  20  15.0\n7      2028802   上海      10215        本科      10  20  15.0\n9      2536296   上海      47993        本科      10  20  15.0"
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(['avg','city'],ascending=False).head(10)#按照两个或多个参数进行排序，在by里用列表括上参数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>city</th>\n      <th>companyId</th>\n      <th>education</th>\n      <th>bottom</th>\n      <th>top</th>\n      <th>avg</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2245819</td>\n      <td>上海</td>\n      <td>130876</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>3</td>\n      <td>2.5</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1605795</td>\n      <td>上海</td>\n      <td>58109</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>4</td>\n      <td>3.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2392372</td>\n      <td>北京</td>\n      <td>48294</td>\n      <td>硕士</td>\n      <td>4</td>\n      <td>8</td>\n      <td>6.0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2527100</td>\n      <td>上海</td>\n      <td>57577</td>\n      <td>本科</td>\n      <td>3</td>\n      <td>4</td>\n      <td>3.5</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2531473</td>\n      <td>上海</td>\n      <td>7069</td>\n      <td>本科</td>\n      <td>4</td>\n      <td>6</td>\n      <td>5.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "   positionId city  companyId education  bottom top  avg\n0     2245819   上海     130876        本科       2   3  2.5\n1     1605795   上海      58109        本科       2   4  3.0\n2     2392372   北京      48294        硕士       4   8  6.0\n3     2527100   上海      57577        本科       3   4  3.5\n4     2531473   上海       7069        本科       4   6  5.0"
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_index().head()#按照索引进行排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>city</th>\n      <th>companyId</th>\n      <th>education</th>\n      <th>bottom</th>\n      <th>top</th>\n      <th>avg</th>\n      <th>rank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10</th>\n      <td>2439826</td>\n      <td>上海</td>\n      <td>54000</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>30</td>\n      <td>22.5</td>\n      <td>1.5</td>\n    </tr>\n    <tr>\n      <th>20</th>\n      <td>2188681</td>\n      <td>上海</td>\n      <td>122536</td>\n      <td>本科</td>\n      <td>20</td>\n      <td>25</td>\n      <td>22.5</td>\n      <td>1.5</td>\n    </tr>\n    <tr>\n      <th>38</th>\n      <td>2224743</td>\n      <td>上海</td>\n      <td>75569</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>25</td>\n      <td>20.0</td>\n      <td>3.0</td>\n    </tr>\n    <tr>\n      <th>34</th>\n      <td>2375192</td>\n      <td>北京</td>\n      <td>148603</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>23</td>\n      <td>19.0</td>\n      <td>4.0</td>\n    </tr>\n    <tr>\n      <th>29</th>\n      <td>2393474</td>\n      <td>上海</td>\n      <td>78151</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>20</td>\n      <td>17.5</td>\n      <td>5.0</td>\n    </tr>\n    <tr>\n      <th>37</th>\n      <td>1978544</td>\n      <td>北京</td>\n      <td>75604</td>\n      <td>硕士</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n      <td>10.5</td>\n    </tr>\n    <tr>\n      <th>16</th>\n      <td>1533700</td>\n      <td>上海</td>\n      <td>117258</td>\n      <td>本科</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n      <td>10.5</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>2566134</td>\n      <td>上海</td>\n      <td>24587</td>\n      <td>本科</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n      <td>10.5</td>\n    </tr>\n    <tr>\n      <th>32</th>\n      <td>2462306</td>\n      <td>上海</td>\n      <td>99520</td>\n      <td>本科</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n      <td>10.5</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>2274995</td>\n      <td>上海</td>\n      <td>21218</td>\n      <td>本科</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n      <td>10.5</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "    positionId city  companyId education  bottom top   avg  rank\n10     2439826   上海      54000        本科      15  30  22.5   1.5\n20     2188681   上海     122536        本科      20  25  22.5   1.5\n38     2224743   上海      75569        本科      15  25  20.0   3.0\n34     2375192   北京     148603        本科      15  23  19.0   4.0\n29     2393474   上海      78151        本科      15  20  17.5   5.0\n37     1978544   北京      75604        硕士      10  20  15.0  10.5\n16     1533700   上海     117258        本科      10  20  15.0  10.5\n13     2566134   上海      24587        本科      10  20  15.0  10.5\n32     2462306   上海      99520        本科      10  20  15.0  10.5\n11     2274995   上海      21218        本科      10  20  15.0  10.5"
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['rank']=df.avg.rank(ascending=False)#method默认rank计算平均值\r\n",
    "df.sort_values('avg',ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>city</th>\n      <th>companyId</th>\n      <th>education</th>\n      <th>bottom</th>\n      <th>top</th>\n      <th>avg</th>\n      <th>rank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10</th>\n      <td>2439826</td>\n      <td>上海</td>\n      <td>54000</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>30</td>\n      <td>22.5</td>\n      <td>1.0</td>\n    </tr>\n    <tr>\n      <th>20</th>\n      <td>2188681</td>\n      <td>上海</td>\n      <td>122536</td>\n      <td>本科</td>\n      <td>20</td>\n      <td>25</td>\n      <td>22.5</td>\n      <td>1.0</td>\n    </tr>\n    <tr>\n      <th>38</th>\n      <td>2224743</td>\n      <td>上海</td>\n      <td>75569</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>25</td>\n      <td>20.0</td>\n      <td>3.0</td>\n    </tr>\n    <tr>\n      <th>34</th>\n      <td>2375192</td>\n      <td>北京</td>\n      <td>148603</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>23</td>\n      <td>19.0</td>\n      <td>4.0</td>\n    </tr>\n    <tr>\n      <th>29</th>\n      <td>2393474</td>\n      <td>上海</td>\n      <td>78151</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>20</td>\n      <td>17.5</td>\n      <td>5.0</td>\n    </tr>\n    <tr>\n      <th>37</th>\n      <td>1978544</td>\n      <td>北京</td>\n      <td>75604</td>\n      <td>硕士</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n      <td>6.0</td>\n    </tr>\n    <tr>\n      <th>16</th>\n      <td>1533700</td>\n      <td>上海</td>\n      <td>117258</td>\n      <td>本科</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n      <td>6.0</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>2566134</td>\n      <td>上海</td>\n      <td>24587</td>\n      <td>本科</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n      <td>6.0</td>\n    </tr>\n    <tr>\n      <th>32</th>\n      <td>2462306</td>\n      <td>上海</td>\n      <td>99520</td>\n      <td>本科</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n      <td>6.0</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>2274995</td>\n      <td>上海</td>\n      <td>21218</td>\n      <td>本科</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n      <td>6.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "    positionId city  companyId education  bottom top   avg  rank\n10     2439826   上海      54000        本科      15  30  22.5   1.0\n20     2188681   上海     122536        本科      20  25  22.5   1.0\n38     2224743   上海      75569        本科      15  25  20.0   3.0\n34     2375192   北京     148603        本科      15  23  19.0   4.0\n29     2393474   上海      78151        本科      15  20  17.5   5.0\n37     1978544   北京      75604        硕士      10  20  15.0   6.0\n16     1533700   上海     117258        本科      10  20  15.0   6.0\n13     2566134   上海      24587        本科      10  20  15.0   6.0\n32     2462306   上海      99520        本科      10  20  15.0   6.0\n11     2274995   上海      21218        本科      10  20  15.0   6.0"
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['rank']=df.avg.rank(ascending=False,method='min')#min排序\r\n",
    "df.sort_values('avg',ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>city</th>\n      <th>companyId</th>\n      <th>education</th>\n      <th>bottom</th>\n      <th>top</th>\n      <th>avg</th>\n      <th>rank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10</th>\n      <td>2439826</td>\n      <td>上海</td>\n      <td>54000</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>30</td>\n      <td>22.5</td>\n      <td>2.0</td>\n    </tr>\n    <tr>\n      <th>20</th>\n      <td>2188681</td>\n      <td>上海</td>\n      <td>122536</td>\n      <td>本科</td>\n      <td>20</td>\n      <td>25</td>\n      <td>22.5</td>\n      <td>2.0</td>\n    </tr>\n    <tr>\n      <th>38</th>\n      <td>2224743</td>\n      <td>上海</td>\n      <td>75569</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>25</td>\n      <td>20.0</td>\n      <td>3.0</td>\n    </tr>\n    <tr>\n      <th>34</th>\n      <td>2375192</td>\n      <td>北京</td>\n      <td>148603</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>23</td>\n      <td>19.0</td>\n      <td>4.0</td>\n    </tr>\n    <tr>\n      <th>29</th>\n      <td>2393474</td>\n      <td>上海</td>\n      <td>78151</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>20</td>\n      <td>17.5</td>\n      <td>5.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "    positionId city  companyId education  bottom top   avg  rank\n10     2439826   上海      54000        本科      15  30  22.5   2.0\n20     2188681   上海     122536        本科      20  25  22.5   2.0\n38     2224743   上海      75569        本科      15  25  20.0   3.0\n34     2375192   北京     148603        本科      15  23  19.0   4.0\n29     2393474   上海      78151        本科      15  20  17.5   5.0"
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['rank']=df.avg.rank(ascending=False,method='max')#max排序\r\n",
    "df.sort_values('avg',ascending=False).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>city</th>\n      <th>companyId</th>\n      <th>education</th>\n      <th>bottom</th>\n      <th>top</th>\n      <th>avg</th>\n      <th>rank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10</th>\n      <td>2439826</td>\n      <td>上海</td>\n      <td>54000</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>30</td>\n      <td>22.5</td>\n      <td>1.0</td>\n    </tr>\n    <tr>\n      <th>20</th>\n      <td>2188681</td>\n      <td>上海</td>\n      <td>122536</td>\n      <td>本科</td>\n      <td>20</td>\n      <td>25</td>\n      <td>22.5</td>\n      <td>2.0</td>\n    </tr>\n    <tr>\n      <th>38</th>\n      <td>2224743</td>\n      <td>上海</td>\n      <td>75569</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>25</td>\n      <td>20.0</td>\n      <td>3.0</td>\n    </tr>\n    <tr>\n      <th>34</th>\n      <td>2375192</td>\n      <td>北京</td>\n      <td>148603</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>23</td>\n      <td>19.0</td>\n      <td>4.0</td>\n    </tr>\n    <tr>\n      <th>29</th>\n      <td>2393474</td>\n      <td>上海</td>\n      <td>78151</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>20</td>\n      <td>17.5</td>\n      <td>5.0</td>\n    </tr>\n    <tr>\n      <th>37</th>\n      <td>1978544</td>\n      <td>北京</td>\n      <td>75604</td>\n      <td>硕士</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n      <td>15.0</td>\n    </tr>\n    <tr>\n      <th>16</th>\n      <td>1533700</td>\n      <td>上海</td>\n      <td>117258</td>\n      <td>本科</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n      <td>10.0</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>2566134</td>\n      <td>上海</td>\n      <td>24587</td>\n      <td>本科</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n      <td>9.0</td>\n    </tr>\n    <tr>\n      <th>32</th>\n      <td>2462306</td>\n      <td>上海</td>\n      <td>99520</td>\n      <td>本科</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n      <td>13.0</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>2274995</td>\n      <td>上海</td>\n      <td>21218</td>\n      <td>本科</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n      <td>8.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "    positionId city  companyId education  bottom top   avg  rank\n10     2439826   上海      54000        本科      15  30  22.5   1.0\n20     2188681   上海     122536        本科      20  25  22.5   2.0\n38     2224743   上海      75569        本科      15  25  20.0   3.0\n34     2375192   北京     148603        本科      15  23  19.0   4.0\n29     2393474   上海      78151        本科      15  20  17.5   5.0\n37     1978544   北京      75604        硕士      10  20  15.0  15.0\n16     1533700   上海     117258        本科      10  20  15.0  10.0\n13     2566134   上海      24587        本科      10  20  15.0   9.0\n32     2462306   上海      99520        本科      10  20  15.0  13.0\n11     2274995   上海      21218        本科      10  20  15.0   8.0"
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['rank']=df.avg.rank(ascending=False,method='first')#first排序\r\n",
    "df.sort_values('avg',ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "array(['本科', '硕士', '大专', '博士', '不限'], dtype=object)"
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.education.unique()#该类别有多少个唯一值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "本科    30\n大专     4\n不限     2\n硕士     2\n博士     1\nName: education, dtype: int64"
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.education.value_counts()#每个类别各有多少个"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>companyId</th>\n      <th>bottom</th>\n      <th>avg</th>\n      <th>rank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>count</th>\n      <td>3.900000e+01</td>\n      <td>39.000000</td>\n      <td>39.000000</td>\n      <td>39.000000</td>\n      <td>39.000000</td>\n    </tr>\n    <tr>\n      <th>mean</th>\n      <td>2.219721e+06</td>\n      <td>59324.410256</td>\n      <td>8.923077</td>\n      <td>12.038462</td>\n      <td>20.000000</td>\n    </tr>\n    <tr>\n      <th>std</th>\n      <td>4.608439e+05</td>\n      <td>44831.121127</td>\n      <td>3.929541</td>\n      <td>5.045040</td>\n      <td>11.401754</td>\n    </tr>\n    <tr>\n      <th>min</th>\n      <td>6.438530e+05</td>\n      <td>5674.000000</td>\n      <td>2.000000</td>\n      <td>2.500000</td>\n      <td>1.000000</td>\n    </tr>\n    <tr>\n      <th>25%</th>\n      <td>2.047188e+06</td>\n      <td>21741.000000</td>\n      <td>6.000000</td>\n      <td>9.000000</td>\n      <td>10.500000</td>\n    </tr>\n    <tr>\n      <th>50%</th>\n      <td>2.393474e+06</td>\n      <td>48294.000000</td>\n      <td>10.000000</td>\n      <td>12.500000</td>\n      <td>20.000000</td>\n    </tr>\n    <tr>\n      <th>75%</th>\n      <td>2.513485e+06</td>\n      <td>88399.000000</td>\n      <td>10.000000</td>\n      <td>15.000000</td>\n      <td>29.500000</td>\n    </tr>\n    <tr>\n      <th>max</th>\n      <td>2.580990e+06</td>\n      <td>153676.000000</td>\n      <td>20.000000</td>\n      <td>22.500000</td>\n      <td>39.000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "         positionId      companyId     bottom        avg       rank\ncount  3.900000e+01      39.000000  39.000000  39.000000  39.000000\nmean   2.219721e+06   59324.410256   8.923077  12.038462  20.000000\nstd    4.608439e+05   44831.121127   3.929541   5.045040  11.401754\nmin    6.438530e+05    5674.000000   2.000000   2.500000   1.000000\n25%    2.047188e+06   21741.000000   6.000000   9.000000  10.500000\n50%    2.393474e+06   48294.000000  10.000000  12.500000  20.000000\n75%    2.513485e+06   88399.000000  10.000000  15.000000  29.500000\nmax    2.580990e+06  153676.000000  20.000000  22.500000  39.000000"
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()#基础统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "count    39.000000\nmean     12.038462\nstd       5.045040\nmin       2.500000\n25%       9.000000\n50%      12.500000\n75%      15.000000\nmax      22.500000\nName: avg, dtype: float64"
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.avg.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "positionId    39\ncity          39\ncompanyId     39\neducation     39\nbottom        39\ntop           39\navg           39\nrank          39\ndtype: int64"
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>city</th>\n      <th>companyId</th>\n      <th>education</th>\n      <th>bottom</th>\n      <th>top</th>\n      <th>avg</th>\n      <th>rank</th>\n      <th>cumsum</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2245819</td>\n      <td>上海</td>\n      <td>130876</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>3</td>\n      <td>2.5</td>\n      <td>39.0</td>\n      <td>2.5</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1605795</td>\n      <td>上海</td>\n      <td>58109</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>4</td>\n      <td>3.0</td>\n      <td>38.0</td>\n      <td>5.5</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2392372</td>\n      <td>北京</td>\n      <td>48294</td>\n      <td>硕士</td>\n      <td>4</td>\n      <td>8</td>\n      <td>6.0</td>\n      <td>34.0</td>\n      <td>11.5</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2527100</td>\n      <td>上海</td>\n      <td>57577</td>\n      <td>本科</td>\n      <td>3</td>\n      <td>4</td>\n      <td>3.5</td>\n      <td>36.0</td>\n      <td>15.0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2531473</td>\n      <td>上海</td>\n      <td>7069</td>\n      <td>本科</td>\n      <td>4</td>\n      <td>6</td>\n      <td>5.0</td>\n      <td>35.0</td>\n      <td>20.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "   positionId city  companyId education  bottom top  avg  rank  cumsum\n0     2245819   上海     130876        本科       2   3  2.5  39.0     2.5\n1     1605795   上海      58109        本科       2   4  3.0  38.0     5.5\n2     2392372   北京      48294        硕士       4   8  6.0  34.0    11.5\n3     2527100   上海      57577        本科       3   4  3.5  36.0    15.0\n4     2531473   上海       7069        本科       4   6  5.0  35.0    20.0"
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['cumsum']=df.avg.cumsum()#累加\r\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>city</th>\n      <th>companyId</th>\n      <th>education</th>\n      <th>bottom</th>\n      <th>top</th>\n      <th>avg</th>\n      <th>rank</th>\n      <th>cumsum</th>\n      <th>bins</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2245819</td>\n      <td>上海</td>\n      <td>130876</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>3</td>\n      <td>2.5</td>\n      <td>39.0</td>\n      <td>2.5</td>\n      <td>(2.48, 7.5]</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1605795</td>\n      <td>上海</td>\n      <td>58109</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>4</td>\n      <td>3.0</td>\n      <td>38.0</td>\n      <td>5.5</td>\n      <td>(2.48, 7.5]</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2392372</td>\n      <td>北京</td>\n      <td>48294</td>\n      <td>硕士</td>\n      <td>4</td>\n      <td>8</td>\n      <td>6.0</td>\n      <td>34.0</td>\n      <td>11.5</td>\n      <td>(2.48, 7.5]</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2527100</td>\n      <td>上海</td>\n      <td>57577</td>\n      <td>本科</td>\n      <td>3</td>\n      <td>4</td>\n      <td>3.5</td>\n      <td>36.0</td>\n      <td>15.0</td>\n      <td>(2.48, 7.5]</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2531473</td>\n      <td>上海</td>\n      <td>7069</td>\n      <td>本科</td>\n      <td>4</td>\n      <td>6</td>\n      <td>5.0</td>\n      <td>35.0</td>\n      <td>20.0</td>\n      <td>(2.48, 7.5]</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "   positionId city  companyId education  bottom top  avg  rank  cumsum  \\\n0     2245819   上海     130876        本科       2   3  2.5  39.0     2.5   \n1     1605795   上海      58109        本科       2   4  3.0  38.0     5.5   \n2     2392372   北京      48294        硕士       4   8  6.0  34.0    11.5   \n3     2527100   上海      57577        本科       3   4  3.5  36.0    15.0   \n4     2531473   上海       7069        本科       4   6  5.0  35.0    20.0   \n\n          bins  \n0  (2.48, 7.5]  \n1  (2.48, 7.5]  \n2  (2.48, 7.5]  \n3  (2.48, 7.5]  \n4  (2.48, 7.5]  "
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['bins']=pd.cut(df.avg,bins=4)#把df的avg数据进行自动化分割，分为4部分\r\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>city</th>\n      <th>companyId</th>\n      <th>education</th>\n      <th>bottom</th>\n      <th>top</th>\n      <th>avg</th>\n      <th>rank</th>\n      <th>cumsum</th>\n      <th>bins</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2245819</td>\n      <td>上海</td>\n      <td>130876</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>3</td>\n      <td>2.5</td>\n      <td>39.0</td>\n      <td>2.5</td>\n      <td>a</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1605795</td>\n      <td>上海</td>\n      <td>58109</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>4</td>\n      <td>3.0</td>\n      <td>38.0</td>\n      <td>5.5</td>\n      <td>a</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2392372</td>\n      <td>北京</td>\n      <td>48294</td>\n      <td>硕士</td>\n      <td>4</td>\n      <td>8</td>\n      <td>6.0</td>\n      <td>34.0</td>\n      <td>11.5</td>\n      <td>a</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2527100</td>\n      <td>上海</td>\n      <td>57577</td>\n      <td>本科</td>\n      <td>3</td>\n      <td>4</td>\n      <td>3.5</td>\n      <td>36.0</td>\n      <td>15.0</td>\n      <td>a</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2531473</td>\n      <td>上海</td>\n      <td>7069</td>\n      <td>本科</td>\n      <td>4</td>\n      <td>6</td>\n      <td>5.0</td>\n      <td>35.0</td>\n      <td>20.0</td>\n      <td>a</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "   positionId city  companyId education  bottom top  avg  rank  cumsum bins\n0     2245819   上海     130876        本科       2   3  2.5  39.0     2.5    a\n1     1605795   上海      58109        本科       2   4  3.0  38.0     5.5    a\n2     2392372   北京      48294        硕士       4   8  6.0  34.0    11.5    a\n3     2527100   上海      57577        本科       3   4  3.5  36.0    15.0    a\n4     2531473   上海       7069        本科       4   6  5.0  35.0    20.0    a"
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['bins']=pd.cut(df.avg,bins=4,labels=list('abcd'))#把df的avg数据进行自动化分割，分为4部分，等级为abcd\r\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>city</th>\n      <th>companyId</th>\n      <th>education</th>\n      <th>bottom</th>\n      <th>top</th>\n      <th>avg</th>\n      <th>rank</th>\n      <th>cumsum</th>\n      <th>bins</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2245819</td>\n      <td>上海</td>\n      <td>130876</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>3</td>\n      <td>2.5</td>\n      <td>39.0</td>\n      <td>2.5</td>\n      <td>(0, 5]</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1605795</td>\n      <td>上海</td>\n      <td>58109</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>4</td>\n      <td>3.0</td>\n      <td>38.0</td>\n      <td>5.5</td>\n      <td>(0, 5]</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2392372</td>\n      <td>北京</td>\n      <td>48294</td>\n      <td>硕士</td>\n      <td>4</td>\n      <td>8</td>\n      <td>6.0</td>\n      <td>34.0</td>\n      <td>11.5</td>\n      <td>(5, 10]</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2527100</td>\n      <td>上海</td>\n      <td>57577</td>\n      <td>本科</td>\n      <td>3</td>\n      <td>4</td>\n      <td>3.5</td>\n      <td>36.0</td>\n      <td>15.0</td>\n      <td>(0, 5]</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2531473</td>\n      <td>上海</td>\n      <td>7069</td>\n      <td>本科</td>\n      <td>4</td>\n      <td>6</td>\n      <td>5.0</td>\n      <td>35.0</td>\n      <td>20.0</td>\n      <td>(0, 5]</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>2427570</td>\n      <td>北京</td>\n      <td>7502</td>\n      <td>本科</td>\n      <td>6</td>\n      <td>8</td>\n      <td>7.0</td>\n      <td>31.0</td>\n      <td>27.0</td>\n      <td>(5, 10]</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>2549808</td>\n      <td>上海</td>\n      <td>47993</td>\n      <td>大专</td>\n      <td>7</td>\n      <td>14</td>\n      <td>10.5</td>\n      <td>27.0</td>\n      <td>37.5</td>\n      <td>(10, 20]</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>2028802</td>\n      <td>上海</td>\n      <td>10215</td>\n      <td>本科</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n      <td>6.0</td>\n      <td>52.5</td>\n      <td>(10, 20]</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>2480295</td>\n      <td>上海</td>\n      <td>30830</td>\n      <td>本科</td>\n      <td>8</td>\n      <td>15</td>\n      <td>11.5</td>\n      <td>23.0</td>\n      <td>64.0</td>\n      <td>(10, 20]</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>2536296</td>\n      <td>上海</td>\n      <td>47993</td>\n      <td>本科</td>\n      <td>10</td>\n      <td>20</td>\n      <td>15.0</td>\n      <td>7.0</td>\n      <td>79.0</td>\n      <td>(10, 20]</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "   positionId city  companyId education  bottom top   avg  rank  cumsum  \\\n0     2245819   上海     130876        本科       2   3   2.5  39.0     2.5   \n1     1605795   上海      58109        本科       2   4   3.0  38.0     5.5   \n2     2392372   北京      48294        硕士       4   8   6.0  34.0    11.5   \n3     2527100   上海      57577        本科       3   4   3.5  36.0    15.0   \n4     2531473   上海       7069        本科       4   6   5.0  35.0    20.0   \n5     2427570   北京       7502        本科       6   8   7.0  31.0    27.0   \n6     2549808   上海      47993        大专       7  14  10.5  27.0    37.5   \n7     2028802   上海      10215        本科      10  20  15.0   6.0    52.5   \n8     2480295   上海      30830        本科       8  15  11.5  23.0    64.0   \n9     2536296   上海      47993        本科      10  20  15.0   7.0    79.0   \n\n       bins  \n0    (0, 5]  \n1    (0, 5]  \n2   (5, 10]  \n3    (0, 5]  \n4    (0, 5]  \n5   (5, 10]  \n6  (10, 20]  \n7  (10, 20]  \n8  (10, 20]  \n9  (10, 20]  "
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['bins']=pd.cut(df.avg,bins=[0,5,10,20,30,999])#把df的avg数据进行手动化分割，分为4部分\r\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>city</th>\n      <th>companyId</th>\n      <th>education</th>\n      <th>bottom</th>\n      <th>top</th>\n      <th>avg</th>\n      <th>rank</th>\n      <th>cumsum</th>\n      <th>bins</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10</th>\n      <td>2439826</td>\n      <td>上海</td>\n      <td>54000</td>\n      <td>本科</td>\n      <td>15</td>\n      <td>30</td>\n      <td>22.5</td>\n      <td>1.0</td>\n      <td>101.5</td>\n      <td>(20, 30]</td>\n    </tr>\n    <tr>\n      <th>20</th>\n      <td>2188681</td>\n      <td>上海</td>\n      <td>122536</td>\n      <td>本科</td>\n      <td>20</td>\n      <td>25</td>\n      <td>22.5</td>\n      <td>2.0</td>\n      <td>239.5</td>\n      <td>(20, 30]</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "    positionId city  companyId education  bottom top   avg  rank  cumsum  \\\n10     2439826   上海      54000        本科      15  30  22.5   1.0   101.5   \n20     2188681   上海     122536        本科      20  25  22.5   2.0   239.5   \n\n        bins  \n10  (20, 30]  \n20  (20, 30]  "
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('avg>20')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>city</th>\n      <th>companyId</th>\n      <th>education</th>\n      <th>bottom</th>\n      <th>top</th>\n      <th>avg</th>\n      <th>rank</th>\n      <th>cumsum</th>\n      <th>bins</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2245819</td>\n      <td>上海</td>\n      <td>130876</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>3</td>\n      <td>2.5</td>\n      <td>39.0</td>\n      <td>2.5</td>\n      <td>低</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1605795</td>\n      <td>上海</td>\n      <td>58109</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>4</td>\n      <td>3.0</td>\n      <td>38.0</td>\n      <td>5.5</td>\n      <td>低</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2392372</td>\n      <td>北京</td>\n      <td>48294</td>\n      <td>硕士</td>\n      <td>4</td>\n      <td>8</td>\n      <td>6.0</td>\n      <td>34.0</td>\n      <td>11.5</td>\n      <td>低</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2527100</td>\n      <td>上海</td>\n      <td>57577</td>\n      <td>本科</td>\n      <td>3</td>\n      <td>4</td>\n      <td>3.5</td>\n      <td>36.0</td>\n      <td>15.0</td>\n      <td>低</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2531473</td>\n      <td>上海</td>\n      <td>7069</td>\n      <td>本科</td>\n      <td>4</td>\n      <td>6</td>\n      <td>5.0</td>\n      <td>35.0</td>\n      <td>20.0</td>\n      <td>低</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "   positionId city  companyId education  bottom top  avg  rank  cumsum bins\n0     2245819   上海     130876        本科       2   3  2.5  39.0     2.5    低\n1     1605795   上海      58109        本科       2   4  3.0  38.0     5.5    低\n2     2392372   北京      48294        硕士       4   8  6.0  34.0    11.5    低\n3     2527100   上海      57577        本科       3   4  3.5  36.0    15.0    低\n4     2531473   上海       7069        本科       4   6  5.0  35.0    20.0    低"
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['bins']=pd.qcut(df.avg,4,labels=[\"低\",\"中\",\"高\",\"很高\"])#数据分箱，4表示分成四个箱子，边界值分别为四分位数，四分之二分位数和四分之三分位数。\r\n",
    "df.head(5)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.5 64-bit ('base': conda)",
   "name": "python385jvsc74a57bd0ae7890921ac3c17143ff000ac7152addc6614e2051824da39aa37dab63d26d82"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "orig_nbformat": 3
 },
 "nbformat": 4,
 "nbformat_minor": 2
}